﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Shop;
using System.Data;
using System.Data.SqlClient;

namespace YXShop.SQLServerDAL.Shop
{
    public class Shop:IShop
    {
        #region DataBase Operation

        public int Add(YXShop.Model.Shop.Shop model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into yxs_shop(");
            strSql.Append("uid,shopname,shoptype,categories,shoprank,affiche,discription,startdate,enddate,state,shopsign,shopstyle,twodomainname,attestationpic,attestationstate,prestigerank,visitno,attribute,addtime,updatetime,shopstate,shopnum,areatid,shoptest,content)");
            strSql.Append(" values(");
            strSql.Append("@uid,@shopname,@shoptype,@categories,@shoprank,@affiche,@discription,@startdate,@enddate,@state,@shopsign,@shopstyle,@twodomainname,@attestationpic,@attestationstate,@prestigerank,@visitno,@attribute,@addtime,@updatetime,@shopstate,@shopnum,@areatid,@shoptest,@content)");
            strSql.Append(" Select scope_IDENTITY()");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParas(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.GetSingle(strSql.ToString(),parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public int Update(YXShop.Model.Shop.Shop model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [yxs_shop] set ");
            strSql.Append("uid=@uid,shopname=@shopname,shoptype=@shoptype,categories=@categories,shoprank=@shoprank,affiche=@affiche,discription=@discription,startdate=@startdate,enddate=@enddate,state=@state,shopsign=@shopsign,shopstyle=@shopstyle,twodomainname=@twodomainname,attestationpic=@attestationpic,attestationstate=@attestationstate,prestigerank=@prestigerank,visitno=@visitno,attribute=@attribute,addtime=@addtime,updatetime=@updatetime,shopstate=@shopstate,shopnum=@shopnum,areatid=@areatid,shoptest=@shoptest,content=@content");
            strSql.Append(" where sid=@sid ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParas(model);
            return ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        public void Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete  from [yxs_shop] ");
            strSql.Append(" where sid=@sid");
            SqlParameter[] parameter = { new SqlParameter("@sid",SqlDbType.Int,4)};
            parameter[0].Value = id;
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameter);

        }


        public void DeleteAll(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_shop] where sid in(");
            strSql.Append(" "+ids+" )");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }
      
        #endregion

        #region DataBase Load

        public YXShop.Model.Shop.Shop GetModelById(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from yxs_shop ");
            strSql.Append(" where sid=@sid ");
            SqlParameter[] parameter ={
                                          new SqlParameter("@sid",SqlDbType.Int,4)
                                     };
            parameter[0].Value = id;
            YXShop.Model.Shop.Shop model = null;
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString(),parameter))
            {
                while(reader.Read())
                {   
                    model=new YXShop.Model.Shop.Shop();
                    model.Sid = Convert.ToInt32(reader["sid"]);
                    model.Uid = Convert.ToInt32(reader["uid"]);
                    model.Shopname = (string)reader["shopname"];
                    model.Shoptype = Convert.ToInt32(reader["shoptype"]);
                    model.Categories = Convert.ToInt32(reader["categories"]);
                    model.Shoprank = Convert.ToInt32(reader["shoprank"]);
                    model.Affiche = (string)reader["affiche"];
                    model.Discription = (string)reader["discription"];
                    model.Startdate = Convert.ToDateTime(reader["startdate"]);
                    model.Enddate = Convert.ToDateTime(reader["enddate"]);
                    model.State = Convert.ToInt32(reader["state"]);
                    model.Shopsign = (string)reader["shopsign"];
                    model.Shopstyle = Convert.ToInt32(reader["shopstyle"]);
                    model.Twodomainname = (string)reader["twodomainname"];
                    model.Attestationpic = (string)reader["attestationpic"];
                    model.Attestationstate = Convert.ToInt32(reader["attestationstate"]);
                    model.Prestigerank = (int)reader["prestigerank"];
                    model.Visitno = Convert.ToInt32(reader["visitno"]);
                    model.Attribute = (string)reader["attribute"];
                    model.Addtime = Convert.ToDateTime(reader["addtime"]);
                    model.Updatetime = Convert.ToDateTime(reader["updatetime"]);
                    model.Shopstate = Convert.ToInt32(reader["shopstate"]);
                    model.Shopnum = (string)reader["shopnum"];
                    model.Areatid = (string)reader["areatid"];
                    model.Shoptest = (string)reader["shoptest"];
                    model.Content = reader["content"].ToString();
                }
            }
            return model;
        }

        public List<YXShop.Model.Shop.Shop> GetListShopByWhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from yxs_shop ");
            if(where!=null&&where!="")
            {
                strSql.Append(" where "+where+" ");
            }
            List<YXShop.Model.Shop.Shop> shopList = new List<YXShop.Model.Shop.Shop>();
            using (SqlDataReader reader = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    YXShop.Model.Shop.Shop model = new YXShop.Model.Shop.Shop();
                    model.Sid = Convert.ToInt32(reader["sid"]);
                    model.Uid = Convert.ToInt32(reader["uid"]);
                    model.Shopname = (string)reader["shopname"];
                    model.Shoptype = Convert.ToInt32(reader["shoptype"]);
                    model.Categories = Convert.ToInt32(reader["categories"]);
                    model.Shoprank = Convert.ToInt32(reader["shoprank"]);
                    model.Affiche = (string)reader["affiche"];
                    model.Discription = (string)reader["discription"];
                    model.Startdate = Convert.ToDateTime(reader["startdate"]);
                    model.Enddate = Convert.ToDateTime(reader["enddate"]);
                    model.State = Convert.ToInt32(reader["state"]);
                    model.Shopsign = (string)reader["shopsign"];
                    model.Shopstyle = Convert.ToInt32(reader["shopstyle"]);
                    model.Twodomainname = (string)reader["twodomainname"];
                    model.Attestationpic = (string)reader["attestationpic"];
                    model.Attestationstate = Convert.ToInt32(reader["attestationstate"]);
                    model.Prestigerank = (int)reader["prestigerank"];
                    model.Visitno = Convert.ToInt32(reader["visitno"]);
                    model.Attribute = (string)reader["attribute"];
                    model.Addtime = Convert.ToDateTime(reader["addtime"]);
                    model.Updatetime = Convert.ToDateTime(reader["updatetime"]);
                    model.Shopstate = Convert.ToInt32(reader["shopstate"]);
                    model.Shopnum = (string)reader["shopnum"];
                    model.Areatid = (string)reader["areatid"];
                    model.Shoptest = (string)reader["shoptest"];
                    model.Content = reader["content"].ToString();
                    shopList.Add(model);
                }
            }
            return shopList;
        }
        public ChangeHope.DataBase.DataByPage GetList(string strWhere)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("[select] * [from] yxs_shop [where] 1=1 ");
            if(strWhere!=null&&strWhere!="")
            {
                strSql.Append(""+strWhere);
            }
            strSql.Append(" [order by] sid desc");
            dataPage.Sql = strSql.ToString();
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        /// <summary>
        /// 排序所有记录
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList(string orderfield, int pagesize, string Conditions)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] * [from] yxs_shop [where] 1=1 " + Conditions + " " + orderfield;
            dataPage.PageSize = pagesize;
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        #endregion

        #region Other Function
        public IDbDataParameter[] ValueParas(YXShop.Model.Shop.Shop model)
        {

            SqlParameter[] parameters ={
                                          new SqlParameter("@sid",SqlDbType.Int,4),
                                          new SqlParameter("@uid",SqlDbType.Int,4),
                                          new SqlParameter("@shopname",SqlDbType.NVarChar,50),
                                          new SqlParameter("@shoptype",SqlDbType.Int,4),
                                          new SqlParameter("@categories",SqlDbType.Int,4),

                                          new SqlParameter("@shoprank",SqlDbType.Int,4),
                                          new SqlParameter("@affiche",SqlDbType.NVarChar,2000),
                                          new SqlParameter("@discription",SqlDbType.Text,16),
                                          new SqlParameter("@startdate",SqlDbType.DateTime,8),
                                          new SqlParameter("@enddate",SqlDbType.DateTime,8),

                                          new SqlParameter("@state",SqlDbType.Int,4),
                                          new SqlParameter("@shopsign",SqlDbType.VarChar,200),
                                          new SqlParameter("@shopstyle",SqlDbType.Int,4),
                                          new SqlParameter("@twodomainname",SqlDbType.VarChar,50),
                                          new SqlParameter("@attestationpic",SqlDbType.VarChar,200),

                                          new SqlParameter("@attestationstate",SqlDbType.Int,4),
                                          new SqlParameter("@prestigerank",SqlDbType.Int,4),
                                          new SqlParameter("@visitno",SqlDbType.Int,4),
                                          new SqlParameter("@attribute",SqlDbType.VarChar,50),
                                          new SqlParameter("@addtime",SqlDbType.DateTime,8),

                                          new SqlParameter("@updatetime",SqlDbType.DateTime,8),
                                          new SqlParameter("@shopstate",SqlDbType.TinyInt,1),
                                          new SqlParameter("@shopnum",SqlDbType.VarChar,20),
                                          new SqlParameter("@areatid",SqlDbType.VarChar,50),
                                          new SqlParameter("@shoptest",SqlDbType.VarChar,1000),
                                          new SqlParameter("@content",SqlDbType.Text)
                                      };
            parameters[0].Value = model.Sid;
            parameters[1].Value = model.Uid;
            parameters[2].Value = model.Shopname;
            parameters[3].Value = model.Shoptype;
            parameters[4].Value = model.Categories;

            parameters[5].Value = model.Shoprank;
            parameters[6].Value = model.Affiche;
            parameters[7].Value = model.Discription;
            parameters[8].Value = model.Startdate;
            parameters[9].Value = model.Enddate;

            parameters[10].Value = model.State;
            parameters[11].Value = model.Shopsign;
            parameters[12].Value = model.Shopstyle;
            parameters[13].Value = model.Twodomainname;
            parameters[14].Value = model.Attestationpic;

            parameters[15].Value = model.Attestationstate;
            parameters[16].Value = model.Prestigerank;
            parameters[17].Value = model.Visitno;
            parameters[18].Value = model.Attribute;
            parameters[19].Value = model.Addtime;

            parameters[20].Value = model.Updatetime;
            parameters[21].Value = model.Shopstate;
            parameters[22].Value = model.Shopnum;
            parameters[23].Value = model.Areatid;
            parameters[24].Value = model.Shoptest==null?"":model.Shoptest;
            parameters[25].Value = model.Content;

            return parameters;
        }
        #endregion

       
    }
}
